package com.hxwm.service;

import com.hxwm.entity.MapperField;
import com.hxwm.entity.MapperSheet;
import com.hxwm.entity.MapperTable;
import com.hxwm.entity.MapperTableRelation;
import com.hxwm.util.HwLog;
import com.hxwm.util.Util;

import java.io.File;
import java.io.FileWriter;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

public class GenerateScriptService {

    /**
     * 多对一映射，数据来源不同
     * @param statement1
     * @param statement2
     * @param mapperSheet
     * @param outputDirectory
     * @param hwLog
     * @throws Exception
     */
    public static void generateMultiSqlFile(Statement statement1,Statement statement2, MapperSheet mapperSheet, String outputDirectory, HwLog hwLog) throws Exception {
        List<MapperTable> oldTableList = mapperSheet.getOldTableList();
        MapperTable newTable = mapperSheet.getNewTable();
        String whereCondition = mapperSheet.getWhereCondition();

        String newTableFieldSql = mapperSheet.getNewTableFieldSql();
        List<MapperTableRelation> tableRelationList = mapperSheet.getTableRelationList();

        //查询旧表的全字段数据
        String selectQuery1 = "SELECT * FROM " + oldTableList.get(0).getTableName() ;
        String selectQuery2 = "SELECT * FROM " + oldTableList.get(1).getTableName() ;

        hwLog.info("数据库查询语句1：\n" + selectQuery1);
        hwLog.info("数据库查询语句2：\n" + selectQuery2);

        // 执行查询旧表数据，生成插入SQL
        ResultSet resultSet1 = statement1.executeQuery(selectQuery1);
        ResultSet resultSet2 = statement2.executeQuery(selectQuery2);
        Map<String, String> whereConditionMap = Util.parseWhereClause(whereCondition);

        // 创建SQL脚本文件
        String scriptFileName = newTable.getTableName() + "_script.sql";
        File scriptFile = new File(outputDirectory, scriptFileName);
        FileWriter writer = new FileWriter(scriptFile);

        // 拼接sql
        String insertSQL = "INSERT INTO " + newTable.getTableName() + " (" + newTableFieldSql + ") VALUES (";

        // 进行笛卡尔积运算并使用条件过滤
        while (resultSet1.next()) {
            while (resultSet2.next()) {
                // 获取 resultSet1 和 resultSet2 中的列值,使用where条件过滤
                boolean flag = true;//判断是否满足where条件的标志
                for (Map.Entry<String,String> entry:whereConditionMap.entrySet()){
                    String oldValue1 = resultSet1.getString(entry.getKey());
                    String oldValue2 = resultSet2.getString(entry.getValue());
                    if(!oldValue1.equals(oldValue2)){
                        flag = false;
                        break;
                    }
                }
                if(flag){//满足条件的数据直接写入文件
                    String values = "";
                    String multiValues = "";
                    boolean multiFlag = false;
                    for (MapperTableRelation mapperTableRelation : tableRelationList) {
                        MapperField newField = mapperTableRelation.getNewField();
                        String oldTableIndex = mapperTableRelation.getOldTableIndex();
                        String specialCondition = newField.getSpecialCondition();
                        if(!Util.isNullOrEmpty(specialCondition)){
                            if("NOW".equals(specialCondition)){
                                values = values + "NOW(),";
                            }else if("GLOBAL".equals(specialCondition)){
                                values = values + "'" + Util.generateGlobalFlow() + "',";
                            }
                            continue;
                        }
                        //值为空的赋值为null
                        String oldValue;
                        if("0".equals(oldTableIndex)){//0-第一个老表，1-第二个老表
                            oldValue = resultSet1.getString(mapperTableRelation.getOldTableFieldName());
                        }else{
                            oldValue = resultSet2.getString(mapperTableRelation.getOldTableFieldName());
                        }
                        String fieldType = newField.getFieldType().toLowerCase();

                        if (oldValue == null) {
                            values = values + "NULL,";
                        } else if ("".equals(oldValue)) {
                            if (fieldType.startsWith("date") || fieldType.startsWith("time") || fieldType.startsWith("int")
                                    || fieldType.startsWith("double")) {
                                values = values + "NULL,";
                            } else {
                                values = values + "'" + oldValue + "',";
                            }
                        } else {
                            if(fieldType.startsWith("double")){
                                oldValue = oldValue.replace(",","");
                            }
                            if (newField.getEnumMapper() != null && !newField.getEnumMapper().isEmpty()) {
                                oldValue = (String) newField.getEnumMapper().get(oldValue);
                                if(oldValue.contains("|")){
                                    multiValues = oldValue;
                                    multiFlag = true;
                                }
                            }

                            if ("".equals(oldValue)) {
                                values = values + "NULL,";
                            } else {
                                values = values + "'" + oldValue + "',";
                            }
                        }
                    }
                    if(multiFlag) {
                        String[] split = multiValues.split("\\|");
                        for (String s : split) {
                            String sqlValue = values.replace(multiValues,s);
                            writer.write(insertSQL + sqlValue.substring(0, sqlValue.lastIndexOf(",")) + ");" + "\n");
                        }
                    }else{
                        writer.write(insertSQL + values.substring(0, values.lastIndexOf(",")) + ");" + "\n");
                    }
                }
            }
            // 重置 resultSet2 游标以便下一次循环
            resultSet2.beforeFirst();
        }
        hwLog.info("文件生成成功：" + outputDirectory + scriptFileName);
        writer.close();
        // 关闭资源
        resultSet1.close();
        resultSet2.close();
    }

    /**
     * 多对一的表关系映射
     * @param statement1
     * @param mapperSheet
     * @param outputDirectory
     * @param hwLog
     * @throws Exception
     */
    public static void generateMultiSqlFile(Statement statement1, MapperSheet mapperSheet, String outputDirectory, HwLog hwLog) throws Exception{
        List<MapperTable> oldTableList = mapperSheet.getOldTableList();
        MapperTable newTable = mapperSheet.getNewTable();
        String whereCondition = mapperSheet.getWhereCondition();
        String oldTableFieldSql = mapperSheet.getOldTableFieldSql();
        String newTableFieldSql = mapperSheet.getNewTableFieldSql();
        List<MapperTableRelation> tableRelationList = mapperSheet.getTableRelationList();

        String oldTabNameSql = "";
        for (MapperTable mapperTable : oldTableList) {
            oldTabNameSql = oldTabNameSql + mapperTable.getTableName() + ",";
        }
        oldTabNameSql = oldTabNameSql.substring(0,oldTabNameSql.lastIndexOf(","));

        //查询旧表的全字段数据
        String selectQuery = "SELECT " + oldTableFieldSql + " FROM " + oldTabNameSql + " " + whereCondition;
        hwLog.info("数据库查询语句：\n" + selectQuery);

        // 拼接sql
        String insertSQL = "INSERT INTO " + newTable.getTableName() + " (" + newTableFieldSql + ") VALUES (";
        // 创建SQL脚本文件
        String scriptFileName = newTable.getTableName() + "_script.sql";
        File scriptFile = new File(outputDirectory, scriptFileName);
        FileWriter writer = new FileWriter(scriptFile);

        // 执行查询旧表数据，生成插入SQL
        ResultSet resultSet = statement1.executeQuery(selectQuery);

        while (resultSet.next()) {
            String values = "";
            String multiValues = "";
            boolean multiFlag = false;
            for (int i = 0; i < tableRelationList.size(); i++) {
                MapperTableRelation mapperTableRelation = tableRelationList.get(i);
                MapperField newField = mapperTableRelation.getNewField();

                String specialCondition = newField.getSpecialCondition();

                if(!Util.isNullOrEmpty(specialCondition)){
                    if("NOW".equals(specialCondition)){
                        values = values + "NOW(),";
                    }else if("GLOBAL".equals(specialCondition)){
                        values = values + "'" + Util.generateGlobalFlow() + "',";
                    }
                    continue;
                }
                //值为空的赋值为null
                String oldValue = resultSet.getString(mapperTableRelation.getOldTableFieldName());
                String fieldType = newField.getFieldType().toLowerCase();
                if (oldValue == null) {
                    values = values + "NULL,";
                } else if ("".equals(oldValue)) {
                    if (fieldType.startsWith("date") || fieldType.startsWith("time") || fieldType.startsWith("int")
                            || fieldType.startsWith("double")) {
                        values = values + "NULL,";
                    } else {
                        values = values + "'" + oldValue + "',";
                    }
                } else {
                    if(fieldType.startsWith("double")){
                        oldValue = oldValue.replace(",","");
                    }
                    if (newField.getEnumMapper() != null && !newField.getEnumMapper().isEmpty()) {
                        oldValue = (String) newField.getEnumMapper().get(oldValue);
                        if(oldValue.contains("|")){
                            multiValues = oldValue;
                            multiFlag = true;
                        }
                    }
                    if (newField.getDateTimeFlag() != null) {
                        //这里一般时旧日期字段对新日期字段，并且新字段拼接旧time
                        String dateStr = resultSet.getString(newField.getDateTimeFlag());
                        if (dateStr != null && !"".equals(dateStr)) {
                            oldValue = dateStr.replace("-", "") + oldValue;
                        } else {
                            oldValue = "";
                        }
                    }

                    if ("".equals(oldValue)) {
                        values = values + "NULL,";
                    } else {
                        values = values + "'" + oldValue + "',";
                    }
                }
            }
            if(multiFlag) {
                String[] split = multiValues.split("\\|");
                for (String s : split) {
                    String sqlValue = values.replace(multiValues,s);
                    writer.write(insertSQL + sqlValue.substring(0, sqlValue.lastIndexOf(",")) + ");" + "\n");
                }
            }else{
                writer.write(insertSQL + values.substring(0, values.lastIndexOf(",")) + ");" + "\n");
            }
        }
        hwLog.info("文件生成成功：" + outputDirectory + scriptFileName);
        writer.close();
        resultSet.close();
    }


    /**
     * 一对一的表关系映射
     * @param statement
     * @param mapperSheet
     * @param outputDirectory
     * @param hwLog
     * @throws Exception
     */
    public static void generateSqlFile(Statement statement, MapperSheet mapperSheet, String outputDirectory,HwLog hwLog) throws Exception{
        //一对一
        MapperTable oldTable = mapperSheet.getOldTable();
        MapperTable newTable = mapperSheet.getNewTable();

        //获取表信息
        String whereCondition = mapperSheet.getWhereCondition();
        Integer sheetLineNum = mapperSheet.getSheetLineNum();

        //查询旧表的全字段数据
        String selectQuery = "SELECT * FROM " + oldTable.getTableName() + " " + whereCondition;
        hwLog.info("数据库查询语句：\n" + selectQuery);

        // 拼接sql
        String insertSQL = "INSERT INTO " + newTable.getTableName() + " (" + newTable.toString() + ") VALUES (";
        // 创建SQL脚本文件
        String scriptFileName = newTable.getTableName() + "_script.sql";
        File scriptFile = new File(outputDirectory, scriptFileName);
        FileWriter writer = new FileWriter(scriptFile);

        // 执行查询旧表数据，生成插入SQL
        ResultSet resultSet = statement.executeQuery(selectQuery);

        while (resultSet.next()) {
            String values = "";
            for (int i = 0; i < sheetLineNum; i++) {
                MapperField newField = newTable.getTableFields().get(i);
                MapperField oldField = oldTable.getTableFields().get(i);

                String specialCondition = newField.getSpecialCondition();

                if(Util.isNullOrEmpty(newField.getFieldName())){
                    continue;
                }
                if(Util.isNullOrEmpty(oldField.getFieldName())){
                    if(!Util.isNullOrEmpty(newField.getFieldName())){
                        if("NOW".equals(specialCondition)){
                            values = values + "NOW(),";
                        }else if("GLOBAL".equals(specialCondition)){
                            values = values + "'" + Util.generateGlobalFlow() + "',";
                        }else if (mapperSheet.isEmptyFlag()){
                            values = values + "'',";
                        }else{
                            values = values + "NULL,";
                        }
                    }
                    continue;
                }

                //获取旧数据库中字段的值 新表对应值的类型
                String oldValue = resultSet.getString(oldField.getFieldName());
                String fieldType = newField.getFieldType().toLowerCase();

                //优先判断特殊域
                if(!Util.isNullOrEmpty(specialCondition)){
                    if("NOW".equals(specialCondition)){
                        values = values + "NOW(),";
                    }else if("GLOBAL".equals(specialCondition)){
                        values = values + "'" + Util.generateGlobalFlow() + "',";
                    }
                } else if (oldValue == null ) { //值为空的 赋值为 null 还是 “”
                    if(mapperSheet.isEmptyFlag()){
                        if (fieldType.startsWith("date") || fieldType.startsWith("time") || fieldType.startsWith("int")
                                || fieldType.startsWith("double")) {
                            values = values + "NULL,";
                        }else{
                            values = values + "'',";
                        }
                    }else{
                        values = values + "NULL,";
                    }
                } else if("".equals(oldValue)){
                    if (fieldType.startsWith("date") || fieldType.startsWith("time") || fieldType.startsWith("int")
                            || fieldType.startsWith("double")) {
                        values = values + "NULL,";
                    } else {
                        values = values + "'" + oldValue + "',";
                    }
                } else {
                    if(fieldType.startsWith("double")){
                        oldValue = oldValue.replace(",","");
                    }
                    if (newField.getEnumMapper() != null && !newField.getEnumMapper().isEmpty()) {
                        oldValue = (String) newField.getEnumMapper().get(oldValue);
                    }
                    if (newField.getDateTimeFlag() != null) {
                        //这里一般时旧日期字段对新日期字段，并且新字段拼接旧time
                        String dateStr = resultSet.getString(newField.getDateTimeFlag());
                        if (dateStr != null && !"".equals(dateStr)) {
                            oldValue = dateStr.replace("-", "") + oldValue;
                        } else {
                            oldValue = "";
                        }
                    }
                    if ("".equals(oldValue)) {
                        values = values + "NULL,";
                    } else {
                        values = values + "'" + oldValue + "',";
                    }
                }
            }
            writer.write(insertSQL + values.substring(0, values.lastIndexOf(",")) + ");" + "\n");
        }
        hwLog.info("文件生成成功：" + outputDirectory + scriptFileName);
        writer.close();
        resultSet.close();
    }

}
